{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb=xw.Book(r'D:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\t+基础数据金水分.xlsx')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 存货与存货科目匹配"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws1=wb.sheets('存货档案')\n",
    "ws2=wb.sheets('科目档案')\n",
    "df1=ws1.range('a1').expand().options(pd.DataFrame).value  #ws1=存货\n",
    "df2=ws2.range('a1').expand().options(pd.DataFrame).value  #ws2=科目"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将科目档案的数据变形\n",
    "\n",
    "df2['收入/成本科目']=df2['科目名称'].apply(lambda x:'收入' if x[-2:]== '收入' else '成本') # 添加一列来 标识科目是 成本科目 or 收入科目，根据 科目名称的最后两个字符来判断\n",
    "df2['存货名称']=df2.apply(lambda x:x['科目名称'].strip() if x['收入/成本科目']=='成本' else x['科目名称'].replace('收入','').strip() ,axis=1)   #str[:-2] 标识从第一个到倒数第三个字符串（左闭右开）\n",
    "df3=df2.loc[:,['科目编码','科目名称','收入/成本科目','存货名称']].set_index(['存货名称','收入/成本科目']).unstack().swaplevel(axis=1)   # unstack做了一个逆透视\n",
    "df3.columns=df3.columns.map(''.join)  # 这个map好强，得注意学学\n",
    "df3=df3.loc[:,['收入科目编码','收入科目名称','成本科目编码','成本科目名称']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1,df3,left_on='存货名称',right_on='存货名称',how='left').to_clipboard()  #存货档案表  和  变形后的科目档案匹配."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 台账城市与代理商匹配 -跑腿"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "ws1=wb.sheets('往来单位档案')\n",
    "ws2=wb.sheets('往来单位匹配中间表')\n",
    "ws_仓库=wb.sheets('仓库档案')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=ws1.range('a1').expand('table').options(pd.DataFrame,index=False).value  # 往来单位档案\n",
    "df2=ws2.range('a1').expand('down').options(pd.DataFrame,index=False).value  # 往来单位匹配中间表\n",
    "仓库档案=ws_仓库.range('a1').expand('table').options(pd.DataFrame,index=False).value\n",
    "df1=df1.loc[:,['往来单位编码','往来单位名称']]    #筛选往来单位档案中需要使用的列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['城市2']=df2['城市'].apply(lambda x:x[:2] if len(x.replace(\"市\",'').replace('县','')) >2  else (x if len(x.replace(\"市\",'').replace('县',''))<2 else  x.replace(\"市\",'').replace('县','')))  #去掉城市名称里的 市或者县\n",
    "df1['往来单位名称2']=df1['往来单位名称'].apply(lambda x:x.replace('代理商','')).apply(lambda x:x[:2] if len(x.replace(\"市\",'').replace('县','')) >2  else (x if len(x.replace(\"市\",'').replace('县',''))<2 else  x.replace(\"市\",'').replace('县','')))  #去掉往来单位名称里的代理商三个字、市、县也去掉\n",
    "仓库档案['仓库名称2']=仓库档案['仓库名称'].apply(lambda x:x.replace('代理商','')).apply(lambda x:x.replace('代理商','')).apply(lambda x:x[:2] if len(x.replace(\"市\",'').replace('县','')) >2  else (x if len(x.replace(\"市\",'').replace('县',''))<2 else  x.replace(\"市\",'').replace('县','')))  #去掉往来单位名称里的代理商三个字、市、县也去掉"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 用带县或不带县（市）的名字各merge一次，这样的结果就是避免了缺字匹配不上，能最大限度的匹配到可能得结果了。\n",
    "merge1=pd.merge(df2,df1,right_on='往来单位名称2',left_on='城市2',how='left')\n",
    "merge2=pd.merge(merge1,df1,left_on='城市',right_on='往来单位名称2',how='left') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge3=pd.merge(merge2,仓库档案,left_on='城市2',right_on='仓库名称2',how='left')\n",
    "merge4=pd.merge(merge3,仓库档案,left_on='城市',right_on='仓库名称2',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge4.to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 台账城市与代理商匹配 -家政"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb=xw.Book('t+基础数据金水分.xlsx')\n",
    "ws1=wb.sheets('往来单位档案')\n",
    "ws2=wb.sheets('往来单位匹配-家政')\n",
    "ws_仓库=wb.sheets('仓库档案')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=ws1.range('a1').expand('table').options(pd.DataFrame,index=False).value\n",
    "df2=ws2.range('a1').expand('down').options(pd.DataFrame,index=False).value\n",
    "仓库档案=ws_仓库.range('a1').expand('table').options(pd.DataFrame,index=False).value\n",
    "df1=df1.loc[:,['往来单位编码','往来单位名称']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['城市2']=df2['城市'].apply(lambda x:x[:2] if len(x.replace(\"市\",'').replace('县','')) >2  else (x if len(x.replace(\"市\",'').replace('县',''))<2 else  x.replace(\"市\",'').replace('县','')))  #去掉城市名称里的 市或者县\n",
    "df1['往来单位名称2']=df1['往来单位名称'].apply(lambda x:x.replace('家政代理商',''))  #去掉往来单位名称里的代理商三个字\n",
    "仓库档案['仓库名称2']=仓库档案['仓库名称'].apply(lambda x:x.replace('家政代理商',''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 用带县或不带县（市）的名字各merge一次，这样的结果就是避免了缺字匹配不上，能最大限度的匹配到可能得结果了。\n",
    "merge1=pd.merge(df2,df1,right_on='往来单位名称2',left_on='城市2',how='left')\n",
    "merge2=pd.merge(merge1,df1,left_on='城市',right_on='往来单位名称2',how='left') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge3=pd.merge(merge2,仓库档案,left_on='城市2',right_on='仓库名称2',how='left')\n",
    "merge4=pd.merge(merge3,仓库档案,left_on='城市',right_on='仓库名称2',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge4.to_clipboard()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
